package auntie.production.app2.net;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.Date;
import java.util.List;


import android.util.Log;
import android.widget.ArrayAdapter;
import auntie.production.app2.db.auntie_production_app_db.production_details;
import auntie.production.app2.db.auntie_production_app_db.productions;

public class mysql_bakery {

    private static final String LOG = mysql_bakery.class.getName();
    
    private static final String driver = "com.mysql.jdbc.Driver";
    private static final String user = "root";
    private static final String psswd = "password";
    private static final String url = "jdbc:mysql://192.168.0.21:3306/auntie_production_app_db";

    public static void mysql_find_products(final ArrayAdapter<String> ps ) {
      String s = "select " +
      "      concat( p.description, '@', pr.price ) as product_name " +
      "      from " +
      "        overall_pos_db.product p " +
      "       ,overall_pos_db.product_rqcp pr " +
      "       ,overall_pos_db.product_category pc " +
      "       ,overall_pos_db.product_product_category ppc " +
      "      where " +
      "          p.product_id = pr.product_id " +
      "      and pc.product_category_id = ppc.product_category_id " +
      "      and ppc.product_id = p.product_id " +
      "      and pc.name = 'bakery' " +
      "      and p.description like '%' " +
      "      order by p.description asc; ";
      
      Connection conn = null;
      try {
          conn = getConnection();
          PreparedStatement pstmt = conn.prepareStatement(s);
          ResultSet rs = pstmt.executeQuery();
          while(rs.next()) {
              String desc = rs.getString(1);
              ps.add(desc);
          }
      } catch( Exception e ) {
          throw new RuntimeException();
      } finally {
          closeConnection(conn);
      }
      
      
    }
    
    public static void mysql_insert_productions(List<productions> ps) {
        Connection conn = null;

        try {
            conn = getConnection();
            Statement stmt = conn
                    .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);
            conn.setAutoCommit(false);
            hlp_mysql_insert_productions(stmt, ps);
            int[] updateCounts = stmt.executeBatch();

            conn.commit();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeConnection(conn);
        }
    }

    private static void hlp_mysql_insert_productions(Statement stmt,
            List<productions> ps) throws SQLException {

        String fsql = "insert into productions( " 
                + "  id " 
                + " ,inputer "
                + " ,total_amount " 
                + " ,submitted " 
                + " ,created "
                + " ,modified) " 
                + "values( " 
                + "  {0} " 
                + " ,''{1}'' "
                + " ,{2} " 
                + " ,{3} " 
                + " ,''{4,date,yyyy-MM-dd hh:mm:ss}'' "
                + " ,''{5,date,yyyy-MM-dd hh:mm:ss}''); ";

        for (productions p : ps) {
            int id = p.getId();
            String inputer = p.getInputer();
            double total_amount = p.getTotal_amount();
            boolean submitted = p.isSubmitted();
            Date created = p.getCreated();
            Date modified = p.getModified();
            String sql = MessageFormat.format(fsql, id, inputer, total_amount,
                    submitted, created, modified);
            Log.i(LOG, sql);
            stmt.addBatch(sql);
        }

    }

    public static void mysql_insert_production_details(List<production_details> pds) {
        Connection conn = null;

        try {
            conn = getConnection();
            Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);
            
            conn.setAutoCommit(false);
            hlp_mysql_insert_production_details(stmt, pds);
            int[] updateCounts = stmt.executeBatch();

            conn.commit();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeConnection(conn);
        }
    }
    
    private static void hlp_mysql_insert_production_details(Statement stmt,
            List<production_details> pds) throws SQLException {

        String fsql = "insert into production_details ( " +
        "  id " +
        " ,production_id " +
        " ,product_name " +
        " ,sticker " +
        " ,price " +
        " ,quantity " +
        " ,amount " +
        " ,input_time " +
        " ,cancelled " +
        " ,created " +
        ") values ( " +
        "  {0} " +
        " ,{1} " +
        " ,''{2}'' " +
        " ,''{3}'' " +
        " ,{4} " +
        " ,{5} " +
        " ,{6} " +
        " ,''{7,date,yyyy-MM-dd hh:mm:ss}'' " +
        " ,{8} " +
        " ,''{9,date,yyyy-MM-dd hh:mm:ss}'' " +
        "); ";

        for (production_details pd : pds) {
            int id = pd.getId();
            int production_id = pd.getProduction_id();
            String product_name = pd.getProduct_name();
            String sticker = pd.getSticker();
            double price = pd.getPrice();
            double quantity = pd.getQuantity();
            double amount = pd.getAmount();
            Date input_time = pd.getInput_time();
            boolean cancelled = pd.isCancelled();
            Date created = pd.getCreated();

            String sql = MessageFormat.format(fsql, id, production_id, product_name, sticker, price, quantity, amount,
                    input_time, cancelled, created);
            Log.i(LOG, sql);
            stmt.addBatch(sql);
        }

    }

    private static Connection getConnection() throws Exception {
        Class.forName(driver);
        return DriverManager.getConnection(url, user, psswd);
    }

    private static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}
